-- @owner: zhao-pengcheng-g
-- @date: 2022/01/24
-- @testpoint:双表，向表1插数时，触发同时向表2插数，for each row，before ，insert

--step1:建表，插入数据;expect:成功
drop table if exists table_src_0002_1 cascade;
drop table if exists table_src_0002_2 cascade;
create table table_src_0002_1(id1 int, id2 int, id3 int) with (storage_type=ustore)
partition by range(id2)
(
        partition p1 values less than(1),
        partition p2 values less than(100000),
        partition p3 values less than(maxvalue)
);
create table table_src_0002_2(id1 int, id2 int, id3 int) with (storage_type=ustore)
partition by range(id2)
(
        partition p1 values less than(1),
        partition p2 values less than(100000),
        partition p3 values less than(maxvalue)
);

--step2:创建函数，创建触发器;expect:成功
create or replace function tri_insert_func_0002() returns trigger as
$$
declare
begin
insert into table_src_0002_2 values(new.id1, new.id2, new.id3);
return new;
end
$$ language plpgsql;
/
drop trigger if exists insert_trigger_0002 on table_src_0002_1 cascade;
create trigger insert_trigger_0002 before insert on table_src_0002_1
        for each row
                when(new.id1<1000)
                execute procedure tri_insert_func_0002();
/
--step3:向表1插入数据时，触发同时向表2插入数据;expect:成功
select * from table_src_0002_2;
insert into table_src_0002_1 values(1,1,1),(2,2,2);
select * from table_src_0002_2;

--step4:环境清理;expect:成功
drop trigger if exists insert_trigger_0002 on table_src_0002_1 cascade;
drop table if exists table_src_0002_1 cascade;
drop table if exists table_src_0002_2 cascade;
